package study.itheima.mystu2.dao;

import study.itheima.mystu2.domian.Student;
import study.itheima.mystu2.utils.JDBCUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao {

    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement(); //
            String sql = "SELECT * FROM student";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                Student stu = new Student(sid, name, age, birthday);
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con,st,rs);
        }
        return list;
    }

        @Override
        public Student findById (Integer id){
            Student stu = null;
            Connection con = null;
            PreparedStatement st =null;
            ResultSet rs = null;
            try {
                con = JDBCUtils.getConnection();
                String sql = "select * from student where sid=?";
                st = con.prepareStatement(sql);
                st.setInt(1,id);
                rs = st.executeQuery();
                while (rs.next()) {
                    Integer sid = rs.getInt("sid");
                    String name = rs.getString("name");
                    Integer age = rs.getInt("age");
                    Date birthday = rs.getDate("birthday");
                    stu = new Student(sid, name, age, birthday);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(con,st,rs);
            }
            return stu;
        }

        @Override
        public int insert (Student stu){
            Connection con = null;
            PreparedStatement st =null;
            int num = 0;
            try {
                con = JDBCUtils.getConnection();
                String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
                String sql = "INSERT INTO student VALUES (?,?,?,?)";
                st = con.prepareStatement(sql); //
                st.setInt(1,stu.getSid());
                st.setString(2,stu.getName());
                st.setInt(3,stu.getAge());
                st.setDate(4,new java.sql.Date(stu.getBirthday().getTime()));
                num = st.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(con,st);
            }
            return num;
        }

        @Override
        public int update (Student stu){
            Connection con = null;
            PreparedStatement st =null;
            int num = 0;
            try {
                con = JDBCUtils.getConnection();
                String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
                String sql = "UPDATE student SET name=?,age=?,birthday=? WHERE sid=?";
                st = con.prepareStatement(sql); //
                st.setInt(4,stu.getSid());
                st.setString(1,stu.getName());
                st.setInt(2,stu.getAge());
                st.setDate(3,new java.sql.Date(stu.getBirthday().getTime()));
                num = st.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(con,st);
            }
            return num;
        }

        @Override
        public int delete (Integer id){
            Connection con = null;
            PreparedStatement st =null;
            int num = 0;
            try {
                con = JDBCUtils.getConnection();
                String sql = "DELETE FROM student WHERE sid=?";
                st = con.prepareStatement(sql); //
                st.setInt(1,id);
                num = st.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(con,st);
            }
            return num;
        }
    }
